<!DOCTYPE html><html><head><title>MySQL学习笔记（Day009：JSON）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day009json">MySQL学习笔记（Day009：JSON）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day009json">MySQL学习笔记（Day009：JSON）</a><ul>
<li><a href="#一-mysql-json类型">一. MySQL JSON类型</a><ul>
<li><a href="#1-json介绍">1. JSON介绍</a></li>
<li><a href="#2-json格式示例">2. JSON格式示例</a></li>
<li><a href="#3-json-vs-blob">3. JSON VS BLOB</a></li>
<li><a href="#4结构化和非结构化">4.结构化和非结构化</a></li>
<li><a href="#5-json操作示例">5. JSON操作示例</a><ul>
<li><a href="#51-json入门">5.1 JSON入门</a></li>
<li><a href="#52-json常用函数介绍">5.2 JSON常用函数介绍</a></li>
<li><a href="#53-json创建索引">5.3 JSON创建索引</a></li>
</ul>
</li>
</ul>
</li>
<li><a href="#二-附录">二. 附录</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-mysql-json类型">一. MySQL JSON类型</h2>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<h3 id="1-json介绍">1. JSON介绍</h3>

<ul><li><strong>JSON</strong>（<code>J</code>ava<code>S</code>cript <code>O</code>bject <code>N</code>otation）是一种轻量级的数据交换语言，并且是独立于语言的文本格式。</li>
<li>一些<code>NoSQL数据库</code>选择<code>JSON</code>作为其数据存储格式，比如：MongoDB、CouchDB等。</li>
<li>MySQL<code>5.7.x</code>开始支持<strong>JSON</strong>数据类型。</li>
</ul>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/json.html" target="_blank">官方文档(JSON类型)</a></p>
</blockquote>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<h3 id="2-json格式示例">2. JSON格式示例</h3>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 摘自 维基百科</span><br><span class="hljs-comment">--</span><br><br>{<br>     "firstName": "John",    <span class="hljs-comment">-- Key : Value 格式</span><br>     "lastName": "Smith",<br>     "sex": "male",<br>     "age": 25,<br>     "address":              <span class="hljs-comment">-- Key : Value ; 其中 Value 也是一个 Key-Value 的结构</span><br>     {<br>         "streetAddress": "21 2nd Street",<br>         "city": "New York",<br>         "state": "NY",<br>         "postalCode": "10021"<br>     },<br>     "phoneNumber": <br>     [<br>         {<br>           "type": "home",<br>           "number": "212 555-1234"<br>         },<br>         {<br>           "type": "fax",<br>           "number": "646 555-4567"<br>         }<br>     ]<br> }<br></code></pre>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<h3 id="3-json-vs-blob">3. JSON VS BLOB</h3>

<ul><li><p><strong>JSON</strong></p>

<ul>
<li>JSON数据可以做有效性检查;</li>
<li>JSON使得查询性能提升;</li>
<li>JSON支持部分属性索引，通过虚拟列的功能可以对JSON中的部分数据进行索引;</li></ul></li>
<li><p><strong>BLOB</strong></p>

<ul>
<li>BLOB类型无法在数据库层做约束性检查;</li>
<li>BLOB进行查询，需要遍历所有字符串;</li>
<li>BLOB做只能做指定长度的索引;</li></ul></li>
</ul>

<blockquote>
  <p>5.7之前，只能把JSON当作BLOB进行存储。数据库层面无法对JSON数据做一些操作，只能由应用程序处理。</p>
</blockquote>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<h3 id="4结构化和非结构化">4.结构化和非结构化</h3>

<ul><li><p><strong>结构化</strong></p>

<ul>
<li>二维表结构（行和列）</li>
<li>使用SQL语句进行操作</li></ul></li>
<li><p><strong>非结构化</strong></p>

<ul>
<li>使用Key-Value格式定义数据，无结构定义</li>
<li>Value可以嵌套Key-Value格式的数据</li>
<li>使用JSON进行实现</li></ul></li>
</ul>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- SQL创建User表</span><br><span class="hljs-comment">--</span><br><span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">user</span> (<br>    id <span class="hljs-built_in">bigint</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span> auto_increment,<br>    user_name <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>),<br>    age <span class="hljs-built_in">int</span>,<br>    <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>(id)<br>);</span><br></code></pre>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-python hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># JSON定义的User表</span><br><span class="hljs-comment">#</span><br><br>db.user.insert({<br>    user_name:<span class="hljs-string">"tom"</span>,<br>    age:<span class="hljs-number">30</span><br>})<br><br>db.createCollection(<span class="hljs-string">"user"</span>)<br></code></pre>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<h3 id="5-json操作示例">5. JSON操作示例</h3>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<h4 id="51-json入门">5.1 JSON入门</h4>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 创建带json字段的表</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">user</span> (<br>    -&gt; uid <span class="hljs-built_in">int</span> auto_increment,<br>    -&gt; <span class="hljs-keyword">data</span> json,<br>    -&gt; <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>(uid)<br>    -&gt; );</span><br>Query OK, 0 rows affected (0.11 sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 插入json数据</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">values</span> (<br>    -&gt; <span class="hljs-literal">null</span>,  <span class="hljs-comment">-- 自增长数据，可以插入null</span><br>    -&gt; <span class="hljs-string">'{<br>    '</span>&gt; <span class="hljs-string">"name"</span>:<span class="hljs-string">"tom"</span>,<br>    <span class="hljs-string">'&gt; "age":18,<br>    '</span>&gt; <span class="hljs-string">"address"</span>:<span class="hljs-string">"SZ"</span><br>    <span class="hljs-string">'&gt; }'</span><br>    -&gt; );</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">values</span> (<br>    -&gt; <span class="hljs-literal">null</span>,<br>    -&gt; <span class="hljs-string">'{<br>    '</span>&gt; <span class="hljs-string">"name"</span>:<span class="hljs-string">"jim"</span>,<br>    <span class="hljs-string">'&gt; "age":28,<br>    '</span>&gt; <span class="hljs-string">"mail"</span>:<span class="hljs-string">"jim@163.com"</span><br>    <span class="hljs-string">'&gt; }'</span><br>    -&gt; );</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">values</span> ( <span class="hljs-literal">null</span>, <span class="hljs-string">"can you insert it?"</span>);</span>  <span class="hljs-comment">-- 无法插入，因为是JSON类型</span><br>ERROR 3140 (22032): Invalid JSON text: "Invalid value." at position 0 in value (or column) can you <span class="hljs-operator"><span class="hljs-keyword">insert</span> it?.  <span class="hljs-comment">-- 这短话有单引号，但是渲染有问题，所以这里去掉了</span><br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;</span><br>+<span class="hljs-comment">-----+---------------------------------------------------+</span><br>| uid | data                                              |<br>+<span class="hljs-comment">-----+---------------------------------------------------+</span><br>|   1 | {"age": 18, "name": "tom", "address": "SZ"}       |  <span class="hljs-comment">-- 这个json中有address字段</span><br>|   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"} |  <span class="hljs-comment">-- 这个json中有mail字段</span><br>+<span class="hljs-comment">-----+---------------------------------------------------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br></span><br></code></pre>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<h4 id="52-json常用函数介绍">5.2 JSON常用函数介绍</h4>

</div><div id="wmd-preview-section-49" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 使用json_extract提取数据</span><br><span class="hljs-comment">-- 原型 : JSON_EXTRACT(json_doc, path[, path] ...) </span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> json_extract(<span class="hljs-string">'[10, 20, [30, 40]]'</span>, <span class="hljs-string">'$[1]'</span>);</span>                  <br>+<span class="hljs-comment">--------------------------------------------+</span><br>| json_extract('[10, 20, [30, 40]]', '$[1]') |<br>+<span class="hljs-comment">--------------------------------------------+</span><br>| 20                                         |  <span class="hljs-comment">-- 从list中抽取 下标 为1的元素（下标从0开始）</span><br>+<span class="hljs-comment">--------------------------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span><br>    -&gt; json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">'$.name'</span>),    <span class="hljs-comment">-- 提起name字段的数据</span><br>    -&gt; json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">'$.address'</span>)  <span class="hljs-comment">-- 提取address字段的数据</span><br>    -&gt; <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;</span><br>+<span class="hljs-comment">------------------------------+---------------------------------+</span><br>| json_extract(data, '$.name') | json_extract(data, '$.address') |<br>+<span class="hljs-comment">------------------------------+---------------------------------+</span><br>| "tom"                        | "SZ"                            |<br>| "jim"                        | NULL                            |  <span class="hljs-comment">-- jim 没有address字段，填充了NULL</span><br>+<span class="hljs-comment">------------------------------+---------------------------------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- json_object 将list(K-V对)封装成json格式</span><br><span class="hljs-comment">-- 原型 : JSON_OBJECT([key, val[, key, val] ...])</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> json_object(<span class="hljs-string">"name"</span>, <span class="hljs-string">"jery"</span>, <span class="hljs-string">"email"</span>, <span class="hljs-string">"jery@163.com"</span>, <span class="hljs-string">"age"</span>,<span class="hljs-number">33</span>);</span><br>+<span class="hljs-comment">----------------------------------------------------------------+</span><br>| json_object("name", "jery", "email", "jery@163.com", "age",33) |<br>+<span class="hljs-comment">----------------------------------------------------------------+</span><br>| {"age": 33, "name": "jery", "email": "jery@163.com"}           |  <span class="hljs-comment">-- 封装成了K-V对</span><br>+<span class="hljs-comment">----------------------------------------------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">values</span> ( <br>    -&gt; <span class="hljs-literal">null</span>,<br>    -&gt; json_object(<span class="hljs-string">"name"</span>, <span class="hljs-string">"jery"</span>, <span class="hljs-string">"email"</span>, <span class="hljs-string">"jery@163.com"</span>, <span class="hljs-string">"age"</span>,<span class="hljs-number">33</span>)  <span class="hljs-comment">-- 进行封装</span><br>    -&gt; );</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;</span><br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br>| uid | data                                                 |<br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br>|   1 | {"age": 18, "name": "tom", "address": "SZ"}          |<br>|   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}    |<br>|   4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |<br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- json_insert 插入数据</span><br><span class="hljs-comment">-- 原型 : JSON_INSERT(json_doc, path, val[, path, val] ...)</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">set</span> @j = <span class="hljs-string">'{ "a": 1, "b": [2, 3]}'</span>;</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> json_insert(@j, <span class="hljs-string">'$.a'</span>, <span class="hljs-number">10</span>, <span class="hljs-string">'$.c'</span>, <span class="hljs-string">'[true, false]'</span>);</span>                 <br>+<span class="hljs-comment">----------------------------------------------------+</span><br>| json_insert(@j, '$.a', 10, '$.c', '[true, false]') |<br>+<span class="hljs-comment">----------------------------------------------------+</span><br>| {"a": 1, "b": [2, 3], "c": "[true, false]"}        |  <span class="hljs-comment">-- a还是=1，存在的被忽略，不受影响</span><br>+<span class="hljs-comment">----------------------------------------------------+  -- c之前不存在，则插入</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">update</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">set</span> <span class="hljs-keyword">data</span> = json_insert(<span class="hljs-keyword">data</span>, <span class="hljs-string">"$.address_2"</span>, <span class="hljs-string">"BJ"</span>) <span class="hljs-keyword">where</span> uid = <span class="hljs-number">1</span>;</span>  <span class="hljs-comment">-- 插入 addres_2</span><br>Query OK, 1 row affected (0.03 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;</span><br>+<span class="hljs-comment">-----+----------------------------------------------------------------+</span><br>| uid | data                                                           |<br>+<span class="hljs-comment">-----+----------------------------------------------------------------+</span><br>|   1 | {"age": 18, "name": "tom", "address": "SZ", "address_2": "BJ"} |  <span class="hljs-comment">-- 增加了addres_2 : "BJ"</span><br>|   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}              |<br>|   4 | {"age": 33, "name": "jery", "email": "jery@163.com"}           |<br>+<span class="hljs-comment">-----+----------------------------------------------------------------+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- json_merge 合并数据并返回。注意：原数据不受影响</span><br><span class="hljs-comment">-- 原型 : JSON_MERGE(json_doc, json_doc[, json_doc] ...)</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">select</span> json_merge(<span class="hljs-string">'{"name": "x"}'</span>, <span class="hljs-string">'{"id": 47}'</span>);</span>    <span class="hljs-comment">-- 原来有两个JSON             </span><br>+<span class="hljs-comment">-------------------------------------------+</span><br>| json_merge('{"name": "x"}', '{"id": 47}') |<br>+<span class="hljs-comment">-------------------------------------------+</span><br>| {"id": 47, "name": "x"}                   |  <span class="hljs-comment">-- 合并多个JSON</span><br>+<span class="hljs-comment">-------------------------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> <br>    -&gt; json_merge(<br>    -&gt; json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">'$.address'</span>),      <span class="hljs-comment">-- json 1</span><br>    -&gt; json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">'$.address_2'</span>))    <span class="hljs-comment">-- jons 2</span><br>    -&gt; <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">where</span> uid = <span class="hljs-number">1</span>;</span><br>+<span class="hljs-comment">---------------------------------------------------------------------------------+</span><br>| json_merge( json_extract(data, '$.address'), json_extract(data, '$.address_2')) |<br>+<span class="hljs-comment">---------------------------------------------------------------------------------+</span><br>| ["SZ", "BJ"]                                                                    |  <span class="hljs-comment">-- 合并成一个json</span><br>+<span class="hljs-comment">---------------------------------------------------------------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- json_array_append 追加数据</span><br><span class="hljs-comment">-- 原型 : JSON_ARRAY_APPEND(json_doc, path, val[, path, val] ...) </span><br><span class="hljs-comment">-- json_append 在5.7.9 中重命名为 json_array_append</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">set</span> @j = <span class="hljs-string">'["a", ["b", "c"], "d"]'</span>;</span>     <span class="hljs-comment">-- 下标为1的元素中只有["b", "c"]</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> json_array_append(@j, <span class="hljs-string">'$[1]'</span>, <span class="hljs-number">1</span>);</span>                       <br>+<span class="hljs-comment">----------------------------------+</span><br>| json_array_append(@j, '$[1]', 1) |<br>+<span class="hljs-comment">----------------------------------+</span><br>| ["a", ["b", "c", 1], "d"]        |    <span class="hljs-comment">--  现在插入了 数字 1</span><br>+<span class="hljs-comment">----------------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br>mysql&gt; <span class="hljs-keyword">update</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">set</span> <span class="hljs-keyword">data</span> = json_array_append(<br>    -&gt; <span class="hljs-keyword">data</span>,<br>    -&gt; <span class="hljs-string">'$.address'</span>,<br>    -&gt; json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">'$.address_2'</span>))<br>    -&gt; <span class="hljs-keyword">where</span> uid = <span class="hljs-number">1</span>;</span><br>Query OK, 1 row affected (0.02 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;</span><br>+<span class="hljs-comment">-----+------------------------------------------------------------------------+</span><br>| uid | data                                                                   |<br>+<span class="hljs-comment">-----+------------------------------------------------------------------------+</span><br>|   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"], "address_2": "BJ"} | <span class="hljs-comment">--address_2追加到address</span><br>|   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}                      |<br>|   4 | {"age": 33, "name": "jery", "email": "jery@163.com"}                   |<br>+<span class="hljs-comment">-----+------------------------------------------------------------------------+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- json_remove 从json记录中删除数据</span><br><span class="hljs-comment">-- 原型 : JSON_REMOVE(json_doc, path[, path] ...)</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">set</span> @j = <span class="hljs-string">'["a", ["b", "c"], "d"]'</span>;</span>   <br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> json_remove(@j, <span class="hljs-string">'$[1]'</span>);</span><br>+<span class="hljs-comment">-------------------------+</span><br>| json_remove(@j, '$[1]') |<br>+<span class="hljs-comment">-------------------------+</span><br>| ["a", "d"]              |  <span class="hljs-comment">-- 删除了下标为1的元素["b", "c"]</span><br>+<span class="hljs-comment">-------------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">update</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">set</span> <span class="hljs-keyword">data</span> = json_remove(<span class="hljs-keyword">data</span>, <span class="hljs-string">"$.address_2"</span>) <span class="hljs-keyword">where</span> uid = <span class="hljs-number">1</span>;</span><br>Query OK, 1 row affected (0.03 sec)<br>Rows matched: 1  Changed: 1  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;</span><br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br>| uid | data                                                 |<br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br>|   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]}  |  <span class="hljs-comment">-- address_2 的字段删除了</span><br>|   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}    |<br>|   4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |<br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<blockquote>
  <p><a href="http://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html" target="_blank">官方文档(JSON函数)</a></p>
</blockquote>

</div><div id="wmd-preview-section-6305" class="wmd-preview-section preview-content">

<h4 id="53-json创建索引">5.3 JSON创建索引</h4>

<p><code>JSON</code>类型数据本身<code>无法直接</code>创建索引，需要将需要索引的<code>JSON数据</code>重新<code>生成虚拟列(Virtual Columns)</code>之后，对<code>该列</code>进行<code>索引</code></p>

<blockquote>
  <p><a href="https://dev.mysql.com/doc/refman/5.7/en/create-table.html#create-table-secondary-indexes-virtual-columns" target="_blank">官方文档–JSON创建索引</a></p>
</blockquote>

<ul><li><strong>新建表时创建JSON索引</strong></li>
</ul></div><div id="wmd-preview-section-6326" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_inex_1(<br>    -&gt; <span class="hljs-keyword">data</span> json,<br>    -&gt; gen_col <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>) generated always <span class="hljs-keyword">as</span> (json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">'$.name'</span>)),  <span class="hljs-comment">-- 抽取data中的name， 生成新的一列，名字为gen_col</span><br>    -&gt; <span class="hljs-keyword">index</span> idx (gen_col)  <span class="hljs-comment">-- 将gen_col 作为索引</span><br>    -&gt; );</span><br>Query OK, 0 rows affected (0.13 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_index_1；<br><span class="hljs-comment">-- -----省略表格线-----</span><br>| test_index_1 | <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`test_index_1`</span> (<br>  <span class="hljs-string">`data`</span> json <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-string">`gen_col`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>) GENERATED ALWAYS <span class="hljs-keyword">AS</span> (json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">'$.name'</span>)) VIRTUAL,<br>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`idx`</span> (<span class="hljs-string">`gen_col`</span>)<br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4 |<br><span class="hljs-comment">-- -----省略表格线-----</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_index_1(<span class="hljs-keyword">data</span>) <span class="hljs-keyword">values</span> (<span class="hljs-string">'{"name":"tom", "age":18, "address":"SH"}'</span>);</span><br>Query OK, 1 row affected (0.04 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_index_1(<span class="hljs-keyword">data</span>) <span class="hljs-keyword">values</span> (<span class="hljs-string">'{"name":"jim", "age":28, "address":"SZ"}'</span>);</span>      <br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> test_index_1;</span><br>+<span class="hljs-comment">---------------------------------------------+---------+</span><br>| data                                        | gen_col |<br>+<span class="hljs-comment">---------------------------------------------+---------+</span><br>| {"age": 18, "name": "tom", "address": "SH"} | "tom"   |<br>| {"age": 28, "name": "jim", "address": "SZ"} | "jim"   |<br>+<span class="hljs-comment">---------------------------------------------+---------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> json_extract(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.name"</span>) <span class="hljs-keyword">as</span> username <span class="hljs-keyword">from</span> test_index_1 <span class="hljs-keyword">where</span> gen_col=<span class="hljs-string">"tom"</span>;</span>  <span class="hljs-comment">-- 如果这样做，为空，原因如下</span><br>Empty <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">hex</span>(<span class="hljs-string">'"'</span>);</span><br>+<span class="hljs-comment">----------+</span><br>| hex('"') |<br>+<span class="hljs-comment">----------+</span><br>| 22       |  <span class="hljs-comment">-- 双引号的 16进制</span><br>+<span class="hljs-comment">----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> <span class="hljs-keyword">hex</span>(gen_col) <span class="hljs-keyword">from</span> test_index_1;</span><br>+<span class="hljs-comment">--------------+</span><br>| hex(gen_col) |<br>+<span class="hljs-comment">--------------+</span><br>| 226A696D22   |  <span class="hljs-comment">-- 双引号本身也作为了存储内容</span><br>| 22746F6D22   |<br>+<span class="hljs-comment">--------------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> json_extract(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.name"</span>) <span class="hljs-keyword">as</span> username <span class="hljs-keyword">from</span> test_index_1 <span class="hljs-keyword">where</span> gen_col=<span class="hljs-string">'"tom"'</span>;</span>  <span class="hljs-comment">-- 使用'"tome"',用单引号括起来</span><br>+<span class="hljs-comment">----------+</span><br>| username |<br>+<span class="hljs-comment">----------+</span><br>| "tom"    |  <span class="hljs-comment">-- 找到了对应的数据</span><br>+<span class="hljs-comment">----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> json_extract(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.name"</span>) <span class="hljs-keyword">as</span> username <span class="hljs-keyword">from</span> test_index_1 <span class="hljs-keyword">where</span> gen_col=<span class="hljs-string">'"tom"'</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>           id: <span class="hljs-number">1</span><br>  select_type: SIMPLE<br>        <span class="hljs-keyword">table</span>: test_index_1<br>   partitions: <span class="hljs-literal">NULL</span><br>         type: ref <br>possible_keys: idx    <span class="hljs-comment">-- 使用了 key idx</span><br>          <span class="hljs-keyword">key</span>: idx<br>      key_len: <span class="hljs-number">43</span><br>          ref: const<br>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">1</span><br>     filtered: <span class="hljs-number">100.00</span><br>        Extra: <span class="hljs-literal">NULL</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> warning (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">---</span><br><span class="hljs-comment">--- 建立表的时候去掉双引用</span><br><span class="hljs-comment">---</span><br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_index_2 (<br>    -&gt; <span class="hljs-keyword">data</span> json,<br>    -&gt; gen_col <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>) generated always <span class="hljs-keyword">as</span> (<br>    -&gt;    json_unquote(    <span class="hljs-comment">-- 使用json_unquote函数进行去掉双引号</span><br>    -&gt;             json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">"$.name"</span>)<br>    -&gt;    )),<br>    -&gt; <span class="hljs-keyword">key</span> idx(gen_col)<br>    -&gt; );</span><br>Query OK, 0 rows affected (0.13 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> test_index_2;</span><br><span class="hljs-comment">-- -----省略表格线-----</span><br>| test_index_2 | <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`test_index_2`</span> (<br>  <span class="hljs-string">`data`</span> json <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-string">`gen_col`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">10</span>) GENERATED ALWAYS <span class="hljs-keyword">AS</span> (json_unquote(<br>            json_extract(<span class="hljs-keyword">data</span>, <span class="hljs-string">"$.name"</span>)<br>   )) VIRTUAL,<br>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`idx`</span> (<span class="hljs-string">`gen_col`</span>)<br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4 |<br><span class="hljs-comment">-- -----省略表格线-----</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_index_2(<span class="hljs-keyword">data</span>) <span class="hljs-keyword">values</span> (<span class="hljs-string">'{"name":"tom", "age":18, "address":"SH"}'</span>);</span><br>Query OK, 1 row affected (0.03 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> test_index_2(<span class="hljs-keyword">data</span>) <span class="hljs-keyword">values</span> (<span class="hljs-string">'{"name":"jim", "age":28, "address":"SZ"}'</span>);</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> json_extract(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.name"</span>) <span class="hljs-keyword">as</span> username <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span> gen_col=<span class="hljs-string">"tom"</span>;</span>  <span class="hljs-comment">-- 未加单引号</span><br>+<span class="hljs-comment">----------+</span><br>| username |<br>+<span class="hljs-comment">----------+</span><br>| "tom"    |  <span class="hljs-comment">-- 可以找到数据</span><br>+<span class="hljs-comment">----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> json_extract(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.name"</span>) <span class="hljs-keyword">as</span> username <span class="hljs-keyword">from</span> test_index_2 <span class="hljs-keyword">where</span> gen_col=<span class="hljs-string">"tom"</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>           id: <span class="hljs-number">1</span><br>  select_type: SIMPLE<br>        <span class="hljs-keyword">table</span>: test_index_2<br>   partitions: <span class="hljs-literal">NULL</span><br>         type: ref<br>possible_keys: idx   <span class="hljs-comment">-- 使用了 key idx</span><br>          <span class="hljs-keyword">key</span>: idx<br>      key_len: <span class="hljs-number">43</span><br>          ref: const<br>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">1</span><br>     filtered: <span class="hljs-number">100.00</span><br>        Extra: <span class="hljs-literal">NULL</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> warning (<span class="hljs-number">0.00</span> sec)<br></span><br></code></pre>

<ul><li><strong>修改已存在的表创建JSON索引</strong></li>
</ul></div><div id="wmd-preview-section-6027" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 使用之前的user表操作</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">user</span>;</span><br><span class="hljs-comment">-- -----省略表格线-----</span><br>| user  | <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`user`</span> (<br>  <span class="hljs-string">`uid`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT,<br>  <span class="hljs-string">`data`</span> json <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`uid`</span>)<br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> AUTO_INCREMENT=<span class="hljs-number">5</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4 |<br><span class="hljs-comment">-- -----省略表格线-----</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;</span><br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br>| uid | data                                                 |<br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br>|   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]}  |<br>|   2 | {"age": 28, "mail": "jim@163.com", "name": "jim"}    |<br>|   4 | {"age": 33, "name": "jery", "email": "jery@163.com"} |<br>+<span class="hljs-comment">-----+------------------------------------------------------+</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">user</span> <br>    -&gt; <span class="hljs-keyword">add</span> user_name <span class="hljs-built_in">varchar</span>(<span class="hljs-number">32</span>)<br>    -&gt; generated always <span class="hljs-keyword">as</span> (json_extract(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.name"</span>)) virtual;</span><br>Query OK, 0 rows affected (0.05 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><span class="hljs-comment">-- virtual 关键字是不将该列的字段值存储，对应的是stored</span><br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> user_name <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span>;</span>  <br>+<span class="hljs-comment">-----------+</span><br>| user_name |<br>+<span class="hljs-comment">-----------+</span><br>| "tom"     |<br>| "jim"     |<br>| "jery"    |<br>+<span class="hljs-comment">-----------+</span><br>3 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">alter</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">add</span> <span class="hljs-keyword">index</span> idx(user_name);</span>          <br>Query OK, 0 rows affected (0.13 sec)<br>Records: 0  Duplicates: 0  Warnings: 0<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">where</span> user_name=<span class="hljs-string">'"tom"'</span>;</span>  <span class="hljs-comment">-- 加单引号</span><br>+<span class="hljs-comment">-----+-----------------------------------------------------+-----------+</span><br>| uid | data                                                | user_name |<br>+<span class="hljs-comment">-----+-----------------------------------------------------+-----------+</span><br>|   1 | {"age": 18, "name": "tom", "address": ["SZ", "BJ"]} | "tom"     |<br>+<span class="hljs-comment">-----+-----------------------------------------------------+-----------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">explain</span> <span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> <span class="hljs-keyword">user</span> <span class="hljs-keyword">where</span> user_name=<span class="hljs-string">'"tom"'</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br>           id: <span class="hljs-number">1</span><br>  select_type: SIMPLE<br>        <span class="hljs-keyword">table</span>: <span class="hljs-keyword">user</span><br>   partitions: <span class="hljs-literal">NULL</span><br>         type: ref<br>possible_keys: idx   <span class="hljs-comment">-- 使用了 key idx</span><br>          <span class="hljs-keyword">key</span>: idx<br>      key_len: <span class="hljs-number">131</span><br>          ref: const<br>         <span class="hljs-keyword">rows</span>: <span class="hljs-number">1</span><br>     filtered: <span class="hljs-number">100.00</span><br>        Extra: <span class="hljs-literal">NULL</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span>, <span class="hljs-number">1</span> warning (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">user</span>;</span><br><span class="hljs-comment">-- -----省略表格线-----</span><br>| user  | <span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`user`</span> (<br>  <span class="hljs-string">`uid`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT,<br>  <span class="hljs-string">`data`</span> json <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br>  <span class="hljs-string">`user_name`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">32</span>) GENERATED ALWAYS <span class="hljs-keyword">AS</span> (json_extract(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.name"</span>)) VIRTUAL,<br>  <span class="hljs-string">`user_name2`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">32</span>) GENERATED ALWAYS <span class="hljs-keyword">AS</span> (json_extract(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.name"</span>)) VIRTUAL,<br>  <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`uid`</span>),<br>  <span class="hljs-keyword">KEY</span> <span class="hljs-string">`idx`</span> (<span class="hljs-string">`user_name`</span>)<br>) <span class="hljs-keyword">ENGINE</span>=<span class="hljs-keyword">InnoDB</span> AUTO_INCREMENT=<span class="hljs-number">5</span> <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=utf8mb4 |<br><span class="hljs-comment">-- -----省略表格线-----</span><br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

<hr></div><div id="wmd-preview-section-4748" class="wmd-preview-section preview-content">

<h2 id="二-附录">二. 附录</h2>

</div><div id="wmd-preview-section-4749" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 老师演示JSON的SQL</span><br><span class="hljs-comment">--</span><br><span class="hljs-operator"><span class="hljs-keyword">drop</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">if</span> <span class="hljs-keyword">exists</span> <span class="hljs-keyword">User</span>;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">User</span> (<br>    uid <span class="hljs-built_in">BIGINT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>,<br>    name <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">32</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>    email <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">256</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>    address <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">512</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br>    <span class="hljs-keyword">UNIQUE</span> <span class="hljs-keyword">KEY</span> (name),<br>    <span class="hljs-keyword">UNIQUE</span> <span class="hljs-keyword">KEY</span> (email)<br>);</span><br><br><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">User</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-literal">NULL</span>,<span class="hljs-string">'David'</span>,<span class="hljs-string">'david@gmail'</span>,<span class="hljs-string">'Shanghai ...'</span>);</span><br><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">User</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-literal">NULL</span>,<span class="hljs-string">'Amy'</span>,<span class="hljs-string">'amy@gmail'</span>,<span class="hljs-string">'Beijing ...'</span>);</span><br><span class="hljs-operator"><span class="hljs-keyword">INSERT</span> <span class="hljs-keyword">INTO</span> <span class="hljs-keyword">User</span> <span class="hljs-keyword">VALUES</span> (<span class="hljs-literal">NULL</span>,<span class="hljs-string">'Tom'</span>,<span class="hljs-string">'tom@gmail'</span>,<span class="hljs-string">'Guangzhou ...'</span>);</span><br><br><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> <span class="hljs-keyword">User</span>;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">User</span> <span class="hljs-keyword">ADD</span> <span class="hljs-keyword">COLUMN</span> address2 <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">512</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>;</span><br><span class="hljs-operator"><span class="hljs-keyword">ALTER</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">User</span> <span class="hljs-keyword">ADD</span> <span class="hljs-keyword">COLUMN</span> passport <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">64</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">DROP</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-keyword">IF</span> <span class="hljs-keyword">EXISTS</span> UserJson;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> UserJson(<br>    uid <span class="hljs-built_in">BIGINT</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> AUTO_INCREMENT <span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span>,<br>    <span class="hljs-keyword">data</span> JSON<br>);</span><br><br><span class="hljs-operator"><span class="hljs-keyword">truncate</span> <span class="hljs-keyword">table</span> UserJson;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> UserJson <br><span class="hljs-keyword">SELECT</span> <br>    uid,JSON_OBJECT(<span class="hljs-string">'name'</span>,name,<span class="hljs-string">'email'</span>,email,<span class="hljs-string">'address'</span>,address) <span class="hljs-keyword">AS</span> <span class="hljs-keyword">data</span><br><span class="hljs-keyword">FROM</span><br>    <span class="hljs-keyword">User</span>;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> * <span class="hljs-keyword">FROM</span> UserJson;</span> <br><br><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> uid,JSON_EXTRACT(<span class="hljs-keyword">data</span>,<span class="hljs-string">'$.address2'</span>) <span class="hljs-keyword">from</span> UserJson;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">UPDATE</span> UserJson<br><span class="hljs-keyword">set</span> <span class="hljs-keyword">data</span> = json_insert(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.address2"</span>,<span class="hljs-string">"HangZhou ..."</span>)<br><span class="hljs-keyword">where</span> uid = <span class="hljs-number">1</span>;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">SELECT</span> JSON_EXTRACT(<span class="hljs-keyword">data</span>,<span class="hljs-string">'$.address[1]'</span>) <span class="hljs-keyword">from</span> UserJson;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">select</span> json_merge(JSON_EXTRACT(<span class="hljs-keyword">data</span>,<span class="hljs-string">'$.address'</span>) ,JSON_EXTRACT(<span class="hljs-keyword">data</span>,<span class="hljs-string">'$.address2'</span>)) <br><span class="hljs-keyword">from</span> UserJson;</span><br><br><span class="hljs-operator"><span class="hljs-keyword">begin</span>;</span><br><span class="hljs-operator"><span class="hljs-keyword">UPDATE</span> UserJson<br><span class="hljs-keyword">set</span> <span class="hljs-keyword">data</span> = json_array_append(<span class="hljs-keyword">data</span>,<span class="hljs-string">"$.address"</span>,JSON_EXTRACT(<span class="hljs-keyword">data</span>,<span class="hljs-string">'$.address2'</span>))<br><span class="hljs-keyword">where</span> JSON_EXTRACT(<span class="hljs-keyword">data</span>,<span class="hljs-string">'$.address2'</span>) <span class="hljs-keyword">IS</span> <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span> <span class="hljs-keyword">AND</span> uid &gt;<span class="hljs-number">0</span>;</span><br><span class="hljs-operator"><span class="hljs-keyword">select</span> JSON_EXTRACT(<span class="hljs-keyword">data</span>,<span class="hljs-string">'$.address'</span>) <span class="hljs-keyword">from</span> UserJson;</span><br><span class="hljs-operator"><span class="hljs-keyword">UPDATE</span> UserJson<br><span class="hljs-keyword">set</span> <span class="hljs-keyword">data</span> = JSON_REMOVE(<span class="hljs-keyword">data</span>,<span class="hljs-string">'$.address2'</span>)<br><span class="hljs-keyword">where</span> uid&gt;<span class="hljs-number">0</span>;</span><br><span class="hljs-operator"><span class="hljs-keyword">commit</span>;</span><br></code></pre></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>